﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.IO;
using System.Xml;
using System.Data.SqlClient;

namespace Server
{
    /// <summary>
    /// Summary description for Service1
    /// </summary>
    [WebService(Namespace = "http://localhost:6442/se137/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
    [System.Web.Script.Services.ScriptService]
    public class Service1 : System.Web.Services.WebService
    {

        /*
         * "SELECT T.BID, B.address1, B.address2, B.address3, B.city, B.distance, " + 
                            "B.latitude, B.longitude, B.mobile_url, B.name, B.nearby_url, B.phone, " + 
                            "B.photo_url, B.photo_url_small, B.rating_img_url, B.rating_img_url_small, " + 
                            "B.state_code, B.url, B.zip, T.AVERAGE_REVIEW
         */
        private static string[] listFullProfile = {"BID",
                                "address1",
                                "address2",
                                "address3",
                                "city",
                                "latitude",
                                "longitude",
                                "name",
                                "phone",
                                "photo_url",
                                "state_code", 
                                "url", 
                                "zip", 
                                "AVERAGE_REVIEW"};

        private static string connectionString = Conn.connectionString;
        [WebMethod]
        public string HelloWorld()
        {
            return "Hello World";
        }
        [WebMethod]
        public string FullProfile(string bid) 
        {
            // Open DB Connection
            SqlConnection conn = new SqlConnection(connectionString);
            conn.Open();

            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "SELECT T.BID, B.address1, B.address2, B.address3, B.city, " + 
                            "B.latitude, B.longitude, B.mobile_url, B.name, B.nearby_url, B.phone, " + 
                            "B.photo_url, B.photo_url_small, B.rating_img_url, B.rating_img_url_small, " + 
                            "B.state_code, B.url, B.zip, T.AVERAGE_REVIEW FROM (SELECT BID, AVG(rating) " +
                            "AS 'AVERAGE_REVIEW' from REVIEWS GROUP BY BID) T, BUSINESSES B " + 
                            "where T.BID = B.BID AND T.BID = '" + bid + "'";
            SqlDataReader rdr = cmd.ExecuteReader();

            // Start the XML
            StringWriter stringWriter = new StringWriter();
            XmlTextWriter xmltextWriter = new XmlTextWriter(stringWriter);

            xmltextWriter.Formatting = Formatting.Indented;
            // Start document
            xmltextWriter.WriteStartDocument();
            xmltextWriter.WriteStartElement("root");
            if (rdr.Read())
            {
                foreach (string field in listFullProfile)
                {
                    try
                    {
                        xmltextWriter.WriteElementString(field, rdr.GetValue(rdr.GetOrdinal(field)).ToString());
                    }
                    catch (Exception ex) 
                    {
                        System.Console.Out.WriteLine(field);
                    }
                }

                // get specialties
                if (rdr.IsClosed == false) rdr.Close();
                cmd.CommandText = "SELECT [NAME] FROM [CATEGORIES], [CATEGORIZEDBUS]" + 
                    " WHERE CATEGORIES.CATID = CATEGORIZEDBUS.CATID AND CATEGORIZEDBUS.BID = '" + bid + "';";
                xmltextWriter.WriteStartElement("specialties");
                rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    try
                    {
                        xmltextWriter.WriteElementString("category", rdr.GetValue(rdr.GetOrdinal("name")).ToString());
                    }
                    catch (Exception ex) { }
                }
                xmltextWriter.WriteEndElement();

                // get reviews
                if (rdr.IsClosed == false) rdr.Close();
                // name, text, date, rating
                cmd.CommandText = "SELECT USERNAME, TEXT_EXCERPT AS TEXT, DATE, RATING FROM REVIEWS, USERS where REVIEWS.USERID = USERS.USERID AND REVIEWS.BID = '" + bid + "' ORDER BY DATE DESC;";
                rdr = cmd.ExecuteReader();
                while (rdr.Read()) 
                {
                    xmltextWriter.WriteStartElement("entry");
                    try 
                    {
                        xmltextWriter.WriteElementString("username", rdr.GetValue(rdr.GetOrdinal("username")).ToString());
                        xmltextWriter.WriteElementString("text", rdr.GetValue(rdr.GetOrdinal("text")).ToString());
                        xmltextWriter.WriteElementString("date", rdr.GetValue(rdr.GetOrdinal("date")).ToString());
                        xmltextWriter.WriteElementString("rating", rdr.GetValue(rdr.GetOrdinal("rating")).ToString());
                    }
                    catch (Exception ex) { }
                    xmltextWriter.WriteEndElement();
                }
            }
            else
            { 
                // error - not found
                xmltextWriter.WriteElementString("error", "not found");
            }

            xmltextWriter.WriteEndElement();
            // Close Connection
            conn.Close();

            // End Document
            xmltextWriter.WriteEndDocument();

            xmltextWriter.Flush();
            xmltextWriter.Close();
            stringWriter.Flush();

            return stringWriter.ToString();
        }
    }
}